﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Infragistics.Excel;

namespace PTExpress.Models
{
    public class XlsProvider
    {
        #region Property

        #endregion

        #region 'Method'
        //Import DataTable from Excel
        static public DataTable GetDataFromExcel(string fileName)
        {
            DataTable tb = new DataTable();
            Workbook wb = Workbook.Load(fileName);
            if (wb == null)
                return tb;
            Worksheet ws = wb.Worksheets[0];
            foreach (WorksheetColumn cl in ws.Columns)
                tb.Columns.Add(ws.Rows[0].Cells[cl.Index].Value.ToString());
            int nRow = ws.Rows.Count();
            for (int i = 1; i < nRow; i++)
            {
                List<string> r = new List<string>();
                foreach (WorksheetCell cell in ws.Rows[i].Cells)
                    r.Add(cell.Value.ToString());
                tb.Rows.Add(r.ToArray());
            }
            return tb;
        }

        //Export DataTable To Excel
        static public bool ExportToExcel(DataTable tb, string fields)
        {
            try
            {
                Workbook wb = new Workbook();
                Worksheet ws;

                int iRow = 0;
                int iCell = 0;
                string[] field = fields.Split(';');
                ws = wb.Worksheets.Add("Sheet1");
                //WorksheetMergedCellsRegion CellMerge = ws.MergedCellsRegions.Add(1, 1, 2, field.Length);
                //CellMerge.CellFormat.Font.Bold = ExcelDefaultableBoolean.True;
                //CellMerge.CellFormat.Font.Italic = ExcelDefaultableBoolean.False;
                //CellMerge.CellFormat.FillPatternForegroundColor = System.Drawing.Color.Blue;
                //CellMerge.CellFormat.VerticalAlignment = VerticalCellAlignment.Center;
                //CellMerge.Value = title;
                //CellMerge.CellFormat.Font.Height = 2000;

                int nRow = tb.Rows.Count;

                //System.Windows.Threading.Dispatcher.CurrentDispatcher.Invoke(System.Windows.Threading.DispatcherPriority.Background,
                //                       new System.Threading.ThreadStart(delegate {
                foreach (string f in field)
                {
                    string[] s = f.Split(':');
                    ws.Rows[iRow].Cells[iCell].CellFormat.Alignment = HorizontalCellAlignment.Center;
                    ws.Columns[iCell].Width = int.Parse(s[2]) * 40;
                    ws.Rows[iRow].Cells[iCell].Value = s[1];
                    ws.Rows[iRow++].Cells[iCell].CellFormat.Font.Color = System.Drawing.Color.Blue;
                    for (int i = 0; i < nRow; i++)
                        ws.Rows[iRow + i].Cells[iCell].Value = tb.Rows[i][s[0]].ToString();
                    iCell++;
                    iRow = 0;
                }
                //}));
                //Mở Dialog SaveFile
                // wb.Save(fileName);
                System.Windows.Forms.SaveFileDialog sFile = new System.Windows.Forms.SaveFileDialog();
                sFile.Filter = "Excel 2003 and before format|*.xls|Excel 2007|*.xlsx";
                sFile.InitialDirectory = System.Windows.Forms.Application.StartupPath;
                sFile.Title = "Save as Excel File";

                if (sFile.ShowDialog() == System.Windows.Forms.DialogResult.OK && sFile.FileName != "")
                    wb.Save(sFile.FileName);
                else
                    return false;
                return true;
            }
            catch { return false; }
        }
        #endregion
    }
}
